package com.trsjh.db.utils;

import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jxl.HeaderFooter;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class ExcelUtil {

	/**
	 * 生成excel
	 * @param path 文件路径
	 * @param title 表格标题
	 * @param head 表头 
	 * @param coments 内容 
	 * @throws IOException 读写异常
	 */
	public static  void generateExcel(String path,String title,String[] head,List<Object[]> coments) throws IOException{
		//String path="d:/test/a.xls";
		System.out.println("file locate:"+path);
		int[] length=new int[head.length];
		WritableWorkbook wwb = null; 
		try {   
			//首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象   
			wwb = Workbook.createWorkbook(new File(path));   
		} catch (IOException e) {   
			e.printStackTrace();  
			throw e;
		}   
		if(wwb!=null){   
			//创建一个可写入的工作表   
			//Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置   
			WritableSheet ws = wwb.createSheet("发布表单", 0);   
			//标题
			WritableFont wfTitle = new WritableFont(WritableFont.TAHOMA, 30,
					WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.RED); // 定义格式 字体 下划线 斜体 粗体 颜色
			WritableCellFormat wcfTtile = new WritableCellFormat(wfTitle); // 单元格定义
			try {
				wcfTtile.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
			} catch (WriteException e1) {
				e1.printStackTrace();
				throw new IOException(e1);
			}
			try {
				ws.mergeCells(0, 0, head.length-1, 0);
			} catch (RowsExceededException e2) {
				e2.printStackTrace();
				throw new IOException(e2);
			} catch (WriteException e2) {
				e2.printStackTrace();
				throw new IOException(e2);
			}
			Label label = new Label(0, 0, title,wcfTtile);
			try {   
				//将生成的单元格添加到工作表中   
				ws.addCell(label);   
			} catch (RowsExceededException e) {   
				e.printStackTrace();   
				throw new IOException(e);
			} catch (WriteException e) {   
				e.printStackTrace();   
				throw new IOException(e);
			}  

			//表头
			WritableFont wfTr = new WritableFont(WritableFont.ARIAL, 15,
					WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLUE_GREY); // 定义格式 字体 下划线 斜体 粗体 颜色
			WritableCellFormat wcfTr = new WritableCellFormat(wfTr); // 单元格定义
			try {
				wcfTr.setBackground(jxl.format.Colour.BLACK);
				// 设置单元格的背景颜色
				wcfTr.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
			} catch (WriteException e1) {
				e1.printStackTrace();
				throw new IOException(e1);

			}
			Label labelh=null;
			int i=0;
			for(String h:head){
				length[i]=length[i]>h.length()?length[i]:h.length(); 
				labelh = new Label(i++, 1,h,wcfTr);
				try {
					ws.addCell(labelh);
				} catch (RowsExceededException e) {
					e.printStackTrace();
					throw new IOException(e);

				} catch (WriteException e) {
					e.printStackTrace();
					throw new IOException(e);

				}
			}
			//内容
			WritableFont wfTd = new WritableFont(WritableFont.ARIAL, 15,
					WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
			WritableCellFormat wcfTd = new WritableCellFormat(wfTd); // 单元格定义
			try {
				wcfTd.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
			} catch (WriteException e1) {
				e1.printStackTrace();
				throw new IOException(e1);

			}

			int j=2;
			for(Object[] objs:coments){
				i=0;
				for(Object obj:objs){
					if(obj==null){
						obj="";
					}
					String str=obj.toString();
					Object o=null;
					length[i]=length[i]>obj.toString().length()?length[i]:obj.toString().length(); 	
					try{
						o=Double.parseDouble(str);
						Number number=new Number(i++,j,(Double)o,wcfTd);
						try {	
							ws.addCell(number);
						} catch (RowsExceededException e) {
							e.printStackTrace();
							throw new IOException(e);

						} catch (WriteException e) {
							e.printStackTrace();
							throw new IOException(e);
						}
					}catch(Exception e1){
						if(obj instanceof Date){
							System.out.println(obj);
							try{
							SimpleDateFormat sdf=new SimpleDateFormat("yyyy年 MM月 dd日");
							
							Label labelc = new Label(i++, j,sdf.format((Date)obj),wcfTd); 
							try {	
								ws.addCell(labelc);
							} catch (RowsExceededException e) {
								e.printStackTrace();
								throw new IOException(e);

							} catch (WriteException e) {
								e.printStackTrace();
								throw new IOException(e);
							}			
							}catch(Exception e2){
								e2.printStackTrace();
							}
						}else{
							o=str;
							Label labelc = new Label(i++, j, obj.toString(),wcfTd); 
							try {
								ws.addCell(labelc);
							} catch (RowsExceededException e) {
								e.printStackTrace();
								throw new IOException(e);

							} catch (WriteException e) {
								e.printStackTrace();
								throw new IOException(e);
							}
						}
					}
				}
				j++;
			}
			//设置列宽
			for(j=0;j<length.length;j++){
				ws.setColumnView(j, length[j]*3);
			}
			setHeader(ws,"ylm", "2012-03-05", "第1页,共1页");
			try {   
				//从内存中写入文件中   
				wwb.write();   
				//关闭资源，释放内存   
				wwb.close();   
				System.out.println("done!");
			} catch (IOException e) {   
				e.printStackTrace();   
			} catch (WriteException e) {   
				e.printStackTrace();   
			}   
		}   

	}


	/**
	 * @param args
	 * @throws IOException 
	 */
	/*	public static void main(String[] args) throws IOException {
		List <Object[]> list=new ArrayList<Object[]>(3);
		list.add(new Object[]{4,"ylm组长",new BigDecimal(22),"啦啦啦啦啦啦啦啦啦啦啦啦啦哈哈哈哈哈哈哈 我是大傻瓜"});
		list.add(new Object[]{4,"ylm总监",new Date(),"呵呵"});
		list.add(new Object[]{4,"ylm组长1",new BigDecimal(22)," 我是大傻瓜"});//new BigDecimal(20)
		list.add(new Object[]{4,"ylm组长2",new BigDecimal(22),"我是大傻瓜"});
		generateExcel("d:/b.xls", "测试", new String[]{"序号","名称","年龄","备注"}, list);
	}   
	 */
	/**向Excel中加入页眉页脚  
	 * @param dataSheet 待加入页眉的工作表  
	 * @param left  
	 * @param center  
	 * @param right  
	 */  
	public static void setHeader(WritableSheet dataSheet,String left,String center,String right){   
		HeaderFooter hf = new HeaderFooter();   
		hf.getLeft().append(left);   
		hf.getCentre().append(center);   
		hf.getRight().append(right);   
		//加入页眉   
		dataSheet.getSettings().setHeader(hf);   
		//加入页脚    
		dataSheet.getSettings().setFooter(hf);
	}  
	
	public static void main(String[] args){
		
		System.out.println("generating excel...");
		List<Object[]> list = new ArrayList<Object[]>();
		for (int i=0;i<10;i++) {
			list.add(new Object[] { "1", 2,3,4, "5",6 });
		}
		String excelName = new Date().getTime() + ".xls";
		String excelPath =  excelName;
		try {
			ExcelUtil.generateExcel(excelPath, excelName, new String[] {
					"项目编号", "项目名称", "项目承担部门", "责任人", "项目结算", "开始日期" }, list);

		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

